package org.ekstrabilet.database.statements;

public class Statements {
	
	public static final String SELECT_SOLD_TICKET= "Select * from sold_tickets where row= ? and seat= ? and sectorID= ?";
	public static final String INSERT_TICKET= "Insert into sold_tickets(gameID, sectorID, row, seat, userID ) values (?, ?, ?, ?, ?)";
	
	public static final String INSERT_SECTOR= "Insert into sector(sectorID, rowsNum, seatsNum, token) values(?, ?, ?, ?)";
	public static final String MAX_SECTOR_ID= "Select max(sectorID) as maxID from sector";
	public static final String SELECT_SECTOR= "Select * from sector where sectorID= ?";
	public static final String SELECT_PRICE= "Select price from ticket_price where sectorID= ?";
	public static final String DELETE_SECTOR= "Delete from sector where sectorID= ?";
	
	public static final String INSERT_TRIBUNE= "Insert into tribune(number) values(?)";
	public static final String MAX_TRIBUNE_ID= "Select max(tribuneID) as maxID from tribune";
	public static final String INSERT_TRIB_SECTOR= "Insert into tribune_sector(tribuneID, sectorID) values(?, ?)";
	public static final String SELECT_TRIBUNE= "Select number from tribune where tribuneID= ?";
	public static final String SELECT_TRIB_SECTOR= "Select sectorID from tribune_sector where tribuneID= ?";
	public static final String DELETE_TRIBUNE= "Delete from tribune where tribuneID= ?";
	
	public static final String INSERT_STADIUM= "Insert into stadium(name, city) values(?, ?)";
	public static final String MAX_STADIUM_ID= "Select max(stadiumID) as maxID from stadium";
	public static final String INSERT_STAD_TRIBUNE= "Insert into stadium_tribune(stadiumID, tribuneID) values(?, ?)";
	public static final String DELETE_STADIUM= "Delete from stadium where stadiumID= ?";
	public static final String SELECT_STADIUM= "Select * from stadium where name= ? and city= ?";
	public static final String SELECT_STAD_TRIBUNE= "Select tribuneID from stadium_tribune where stadiumID= ?";
	public static final String SELECT_STAD_SECTOR= "select distinct s.sectorID from sector s join tribune_sector ts on ts.sectorID= s.sectorID" +
													" where s.token like ? and ts.tribuneID= ( SELECT distinct t.tribuneID from tribune t, " +
													"stadium_tribune st where t.number= ? and st.stadiumID= ?)";
	public static final String SELECT_ALL_GAMES= "Select * from game natural join stadium";
	public static final String SELECT_TICKET_PRICE= "Select sectorID, price from ticket_price where gameID= ?";
	public static final String INSERT_GAME= "Insert into game(stadiumID, date, time, teamA, teamB) values(?, ?, ?, ?, ?)";
	public static final String INSERT_TICKET_PRICE= "Insert into ticket_price(gameID, sectorID, price) values(?, ?, ?)";
	public static final String DELETE_GAME= "Delete from game where gameID= ?";
}
